using System;
using System.Collections.Generic;
using System.Data;

namespace SeventhHabit.Dbm.Model {
    public abstract class VersionedDatabaseBase : IVersionedDatabase {
        #region C'tors

        protected VersionedDatabaseBase(string connectionString) {
            ConnectionString = connectionString;
        }

        #endregion

        #region Instance Methods

        protected void ExecuteScriptsBase(IDbConnection connection, string[] sqlScripts, bool transactional) {
            IDbCommand cmd = connection.CreateCommand();
            connection.Open();
            try {
                if (transactional) ExecuteScriptsInTransaction(connection, cmd, sqlScripts);
                else ExecuteScriptsWithoutTransaction(cmd, sqlScripts);
            }
            finally {
                connection.Close();
            }
        }

        protected void RetrieveReleaseNumbers(IDbConnection connection) {
            IDbCommand cmd = connection.CreateCommand();
            const string sqlScript =
                @"SELECT MajorReleaseNumber, MinorReleaseNumber, PointReleaseNumber FROM SchemaChangeLog ORDER BY MajorReleaseNumber DESC, MinorReleaseNumber DESC, PointReleaseNumber DESC";
            IDataReader result;

            connection.Open();
            try {
                result = ExecuteReader(cmd, sqlScript);
                if (result.Read()) {
                    MajorReleaseNumber = result.GetInt16(0);
                    MinorReleaseNumber = result.GetInt16(1);
                    PointReleaseNumber = result.GetInt16(2);
                }
            }
            finally {
                connection.Close();
            }
        }


        protected bool SchemaChangeLogTableExists(IDbConnection connection) {
            IDbCommand cmd = connection.CreateCommand();
            const string sqlScript = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SchemaChangeLog'";
            // const string sqlScript = "SELECT case when object_id('SchemaChangeLog')is not null then 1 else 0 end";
            bool result;

            connection.Open();
            try {
                if (ExecuteScalar(cmd, sqlScript) == null) result = false;
                else result = true;
            }
            finally {
                connection.Close();
            }

            return result;
        }

        private static void ExecuteScriptsWithoutTransaction(IDbCommand cmd, IEnumerable<string> sqlScripts) {
            foreach (string sqlScript in sqlScripts) ExecuteNonQuery(cmd, sqlScript);
        }

        private static void ExecuteScriptsInTransaction(IDbConnection connection, IDbCommand cmd, IEnumerable<string> sqlScripts) {
            IDbTransaction transaction = connection.BeginTransaction();
            cmd.Transaction = transaction;
            try {
                foreach (string sqlScript in sqlScripts) ExecuteNonQuery(cmd, sqlScript);
                transaction.Commit();
            }
            catch (Exception) {
                transaction.Rollback();
                throw;
            }
        }

        #endregion

        #region IVersionedDatabase Members

        public string ConnectionString { get; protected set; }
        public int MajorReleaseNumber { get; protected set; }
        public int MinorReleaseNumber { get; protected set; }
        public int PointReleaseNumber { get; protected set; }

        public abstract void ExecuteScripts(string[] sqlScripts, bool transactional);

        #endregion

        #region Class Methods

        protected static void ExecuteNonQuery(IDbCommand cmd, string script) {
            cmd.CommandText = script;
            cmd.ExecuteNonQuery();
        }

        protected static IDataReader ExecuteReader(IDbCommand cmd, string script) {
            cmd.CommandText = script;
            return cmd.ExecuteReader();
        }

        protected static object ExecuteScalar(IDbCommand cmd, string script) {
            cmd.CommandText = script;
            return cmd.ExecuteScalar();
        }

        #endregion
    }
}